import re
import os
import chardet
import sqlparse
import tkinter as tk
from tkinter import filedialog, messagebox

class SQLValidatorApp:
    def __init__(self, master):
        self.master = master
        self.master.title("SQL Schema 校验工具 by lxx")

        # 文件选择框
        self.file_label = tk.Label(master, text="选择要验证的SQL文件:")
        self.file_label.grid(row=0, column=0, padx=10, pady=5)
        self.file_path_var = tk.StringVar()
        self.file_path_entry = tk.Entry(master, textvariable=self.file_path_var, width=50)
        self.file_path_entry.grid(row=0, column=1, padx=10, pady=5)
        self.browse_button = tk.Button(master, text="浏览", command=self.browse_file)
        self.browse_button.grid(row=0, column=2, padx=10, pady=5)

        # Schema 输入框
        self.schema_label = tk.Label(master, text="输入要校验的 Schema:")
        self.schema_label.grid(row=1, column=0, padx=10, pady=5)
        self.schema_var = tk.StringVar()
        self.schema_entry = tk.Entry(master, textvariable=self.schema_var, width=50)
        self.schema_entry.grid(row=1, column=1, padx=10, pady=5)

        # 表空间校验复选框
        self.tablespace_var = tk.BooleanVar()
        self.tablespace_check = tk.Checkbutton(master, text="校验表空间", variable=self.tablespace_var)
        self.tablespace_check.grid(row=2, column=0, padx=10, pady=5)

        # 开始校验按钮
        self.validate_button = tk.Button(master, text="开始校验", command=self.validate_sql)
        self.validate_button.grid(row=2, column=1, padx=10, pady=5)

        # 错误文件路径结果输出窗口
        self.result_text = tk.Text(master, width=60, height=10, wrap=tk.WORD)
        self.result_text.grid(row=3, column=0, columnspan=3, padx=10, pady=(5, 20))

    def browse_file(self):
        file_path = filedialog.askopenfilename(filetypes=[("SQL Files", "*.sql")])
        self.file_path_var.set(file_path)

    def get_file_encoding(self, file_path):
        with open(file_path, 'rb') as f:
            result = chardet.detect(f.read())
        return result['encoding']

    def remove_comments(self, sql):
        sql = re.sub(r'--.*?$', '', sql, flags=re.MULTILINE)  # 移除行注释
        sql = re.sub(r'/\*.*?\*/', '', sql, flags=re.DOTALL)  # 移除块注释
        return sql

    def read_sql_file(self, file_path):
        encoding = self.get_file_encoding(file_path)
        sql_statements = []
        try:
            with open(file_path, 'r', encoding=encoding) as file:
                raw_sql = file.read()
                raw_sql = self.remove_comments(raw_sql)
                parsed = sqlparse.parse(raw_sql)
                for statement in parsed:
                    sql_statements.append(str(statement).strip())
        except (UnicodeDecodeError, IOError) as e:
            messagebox.showerror("错误", f"读取文件时出错: {e}")
        return sql_statements

    def validate_sql(self):
        file_path = self.file_path_var.get()
        schema = self.schema_var.get()
        check_tablespace = self.tablespace_var.get()

        if not os.path.isfile(file_path):
            messagebox.showerror("错误", "请选择要验证的文件")
            return

        if not schema:
            messagebox.showerror("错误", "请输入要校验的 Schema")
            return

        sql_statements = self.read_sql_file(file_path)

        if not sql_statements:
            messagebox.showinfo("提示", "文件读取失败")
            return

        schema_view_table_pattern = re.compile(r'\b(view|from|table|into)\s+([^\s\(\);]+)', re.IGNORECASE)
        create_table_pattern = re.compile(r'\bcreate\s+table\b', re.IGNORECASE)
        create_index_pattern = re.compile(r'\bcreate\s+(unique\s+)?index\b', re.IGNORECASE)
        tablespace_pattern = re.compile(r'\btablespace\b', re.IGNORECASE)

        problem_sqls = []

        for sql in sql_statements:
            matches = schema_view_table_pattern.findall(sql)
            missing_or_mismatched = False
            for match in matches:
                schema_name = match[1].split('.')[0]
                if schema_name != schema:
                    missing_or_mismatched = True
                    break

            if missing_or_mismatched:
                problem_sqls.append(f'-- 错误: schema 错误或缺失\n{sql}')
                continue

            if check_tablespace:
                if (create_table_pattern.search(sql) or create_index_pattern.search(sql)) and not tablespace_pattern.search(sql):
                    problem_sqls.append(f'-- 错误: 缺少表空间语句\n{sql}')
                    continue

        if problem_sqls:
            error_file_path = file_path.replace('.sql', '_error.sql')
            try:
                with open(error_file_path, 'w', encoding='UTF-8') as error_file:
                    for statement in problem_sqls:
                        error_file.write(statement + '\n\n')
                self.result_text.insert(tk.END, f'问题SQL已保存到 {error_file_path}\n')
            except IOError as e:
                messagebox.showerror("错误", f"保存错误文件时出错: {e}")
        else:
            self.result_text.insert(tk.END, "验证通过\n")

if __name__ == "__main__":
    root = tk.Tk()
    app = SQLValidatorApp(root)
    root.mainloop()
